#!/usr/bin/perl -w

package conf;
    use strict;
    use DBI;
    use utf8;
    use URI::Escape;

	require '../sub/subMail.pl' ;
	require '../conf/config.pl';
    
    our %db_conf = %conf::db_conf ; 
    my $db_host = $db_conf{'db_host'};
    my $db_user = $db_conf{'db_user'};
    my $db_pwd = $db_conf{'db_pwd'}; 

    # Connect to target DB
    my $dbh = DBI->connect("$db_host","$db_user","$db_pwd", {'RaiseError' => 1,'mysql_enable_utf8'=>1});

    my $sqlString="";
    my $subject="";
    my $contents="";
    my $receipt="";

	#获取最近1个小时关闭的报障
	$sqlString = <<"___SQL___";
SELECT t.tn as no,IFNULL(cu.first_name,replace(replace(replace(t.customer_user_id,'"',''),'<',''),'>','')) as customer,t.title as subject,u.first_name as engineer,t.create_time as time,IFNULL(cu.email,replace(t.customer_user_id,'"','')) as customer_mail  
FROM (ticket t left join customer_user cu on cu.customer_id=t.customer_user_id) left join users u on u.id=t.user_id  
where  t.queue_id  in (2,5,6,7,8,14,15,16) and (t.change_time>NOW()-INTERVAL 30 MINUTE ) and t.ticket_state_id in (2,3,9)
___SQL___

    my $sqr = $dbh->prepare("$sqlString");
    $sqr->execute();

    while(my $ref = $sqr->fetchrow_hashref()) {
    	
		my $subsqr = $dbh->prepare("select a.a_body as body,a_from as sender from ticket t left join article a on (a.ticket_id=t.id) where t.tn='$ref->{no}' order by a.change_time");
	
		$subsqr->execute();
		my $subcontents="";
		while(my $subref = $subsqr->fetchrow_hashref()) {
			$subcontents.="$subref->{sender}:\n";
			$subcontents.="---------------------------------------------------------------------------\n";
			$subcontents.="$subref->{body}\n \n";
	
		}    	
    	my $customer=$ref->{'customer'};
    	$receipt="$ref->{'customer_mail'},OTRS admin <meansonw\@hwcl.com.cn>";

		
		use URI::URL;
		my $urlReply="[Ticket#$ref->{'no'}] Still exists/补充意见";
		$urlReply = URI::URL->new( $urlReply );
		$urlReply =~ s/#/%23/g;
		my $urlPraise="[IT-FEEDBACK] $customer PRAISE $ref->{'engineer'}";
		$urlPraise = URI::URL->new( $urlPraise );		
		my $urlComplain="[IT-FEEDBACK] $customer COMPLAIN $ref->{'engineer'}";
		$urlComplain = URI::URL->new( $urlComplain );	
		my $urlNew="这里填写姓名/系统/问题简单描述，用空格隔开";
		$urlNew = URI::URL->new( $urlNew );				

    	$subject="[Ticket#$ref->{'no'}] 报障关闭通知[Closed] （$ref->{'subject'}）";
		$contents = <<"___CONTENTS___";
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">  
		
<style type='text/css'>div{border:0px solid #CCC;} div.h{float:left;width:80px;margin:auto;overflow-x:hidden;} body {background-color:#D5D5D5} table.header {font-size:13pt;background-color:#F3F3F3; width:80%; height:8;border-top: 2px solid #FFFFFF; border-left: 2px solid #FFFFFF; border-right: 2px solid #8995FA; border-bottom: 2px solid #8995FA; } table.rate {font-size:16pt;background-color:#F3F3F3;width:80% } table.maincontent {font-size:11pt;background-color:#F3F3F3;width:80%;border-collapse:collapse;  } table.maincontent td{border:solid #000 1px;} table.content {font-size:10pt;background-color:#F3F3F3;width:80% }
 table.contentG {font-size:11pt;background-color:#FFCCFF;width:80% } table.contentN {font-size:10pt;background-color:#CCCCCC;width:80% } table.footer {font-size:10pt; background-color:#669900;width:80%} P {text-align:left;color:red} .A {width:100%;height:10;text-align:center} .B {text-align:center } .C {color:#AC0000} .D{color:#339933} .E {width:150} .F {font-weight:bold,color:#FF0000} .I {font-weight:bold,color:#FF0000} a:visited{color:blue;text-decoration:none} a:link{text-decoration:none} .H {font-size:11pt;background-color:#669900;}</style>
<html><body><table class=header align='center'><tr><td class=A><a class=F>和黄商贸报障中心 - 通知</a></td></tr></table>
<table class=content align='center'>
<tr><td COLSPAN=2 class=I>
Dear <strong>$customer</strong>,<br><br>
&nbsp;&nbsp;&nbsp;&nbsp;报障 [$ref->{'subject'}] 已关闭。<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;如果您认为此处理结果存在问题,需要继续跟进，可以直接回复此邮件通知支持人员。
<a href="mailto:it\@hwccl.com?subject=$urlReply">点击这里回复，请移除签名</a> <br>
<br>

&nbsp;&nbsp;This ticket ($ref->{'subject'}) has been closed.<br>
&nbsp;&nbsp;Please don't reply to this message unless for some reason you do not feel this ticket needs to be closed.<br>
<br>
【IT服务反馈】<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;如果您对该$ref->{'engineer'}的服务觉得<strong><font color=blue>满意</font></strong>，请
<a href="mailto:it\@hwccl.com?subject=$urlPraise">点击这里感谢$ref->{'engineer'}</a> <br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;对$ref->{'engineer'}的服务<strong><font color=red>不满意</font></strong>，您可以
<a href="mailto:it\@hwccl.com?subject=$urlComplain">点击这里投诉</a> <br>
<br>
【IT服务支持】<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;有其他的IT问题？欢迎
<a href="mailto:it\@hwccl.com">点击这里报障</a> <br>
&nbsp;&nbsp;&nbsp;&nbsp;注：主题请按【姓名/系统/问题简单描述，用空格隔开】格式填写。<br>
&nbsp;&nbsp;&nbsp;&nbsp;和黄商贸办公网站入口 : my.hwccl.com <br>
&nbsp;&nbsp;&nbsp;&nbsp;计算机/网络故障处理方法： 1.联系报障中心：it\@hwccl.com       2.IMO-在搜索栏输入： it <br> 
<br>
<br><br>
处理信息/Quote:
<xmp class=D>
$subcontents
</xmp>
</td></tr>
<tr><td class=B>$ref->{'name'}</td><td class=B>$ref->{'total'}</td></tr>
</table>
___CONTENTS___

	#$receipt='marcoy@hwccl.com,suanh@hwccl.com,meansonw@hwccl.com';
	#$receipt='meansonw@hwccl.com';
	sendMail("$receipt","$subject","$contents");
	#print $receipt;
    }
    


	

exit 0;